--EXEC SP_GET_ALL_RUNNING_LOTS 'FG', 0, 0, 0, false
-- NEEDS TO MODIFY FOR WAFER QTY IN
	
ALTER PROC SP_GET_ALL_RUNNING_LOTS
( 
 @PROCESS_TYPE   VARCHAR(50),
 @PROCESSSTEP_ID INT,
 @LOCATION_ID INT,
 @SEARCH_LOT_ID INT,
 @AVIOD_TR_FG_LOTS BIT 
)
AS
BEGIN
	SET NOCOUNT ON
	CREATE TABLE #RESULTS ( LOT_ID INT,PROCESSSTEP_ID INT, LOCATION_ID INT, PART_NUMBER VARCHAR(50), Lot_Number VARCHAR(50), Fab_Lot_Number VARCHAR(50), WAFER_QTY_IN INT, QTY_IN INT, DATE_IN DATETIME, VENDOR_NAME VARCHAR(50), DATE_CODE VARCHAR(50), NEXT_VENDOR VARCHAR(50), NEXT_PROCESS VARCHAR(50), LOT_STATUS VARCHAR(50), LOT_COMMENTS VARCHAR(500) ) 
	PRINT @AVIOD_TR_FG_LOTS
	DECLARE @QUERYSTRING	VARCHAR(4000)

	SET @QUERYSTRING = N'
 	INSERT INTO #RESULTS 
 	SELECT	LT.LOT_ID, LT.PROCESSSTEP_ID, LT.LOCATION_ID, PART_NUMBER, LOT_NUMBER, FAB_LOT_NUMBER, WAFER_QTY_IN, QTY_IN, DATE_IN, L_VENDOR_NAME, LOT_DATE_CODE, NULL, NULL, LOT_CURRENT_STATUS, LOT_COMMENTS
	FROM LOT_TABLE LT, PROCESS_STEP PS, LOCATION L, PRODUCT P 
	WHERE 
	LT.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND
	LT.LOCATION_ID = L.LOCATION_ID AND ( WAFER_QTY_IN > 0 OR QTY_IN > 0 ) AND LT.LOT_CURRENT_STATUS <> ''Close'' '
	
		IF @PROCESS_TYPE IS NOT NULL AND @PROCESS_TYPE <> '0'
	 	BEGIN
	 		SET @QUERYSTRING = @QUERYSTRING + ' AND PS_TYPE = '''+ @PROCESS_TYPE+''''
	 	END
	 	IF ( @PROCESSSTEP_ID IS NOT NULL AND @PROCESSSTEP_ID > 0  )
	 	BEGIN
	 		SET @QUERYSTRING = @QUERYSTRING +' AND PS.PROCESSSTEP_ID = ' + CONVERT(VARCHAR,@PROCESSSTEP_ID)
	 	END
		IF ( @LOCATION_ID IS NOT NULL AND @LOCATION_ID > 0  )
		BEGIN
	 		SET @QUERYSTRING = @QUERYSTRING +' AND LT.LOCATION_ID = ' + CONVERT(VARCHAR,@LOCATION_ID)
	 	END
	 	IF ( @SEARCH_LOT_ID IS NOT NULL AND @SEARCH_LOT_ID > 0  )
		BEGIN
 			SET @QUERYSTRING = @QUERYSTRING +' AND LT.LOT_ID = ' + CONVERT(VARCHAR,@SEARCH_LOT_ID)
 		END

		SET @QUERYSTRING = @QUERYSTRING + ' ORDER BY P.PART_NUMBER'
	--PRINT '@QUERYSTRING :- '+@QUERYSTRING
	 EXEC (@QUERYSTRING)

	DECLARE @WIPCURSOR CURSOR
	DECLARE @LOT_NUMBER VARCHAR(50)
	DECLARE @LOT_ID INT
	DECLARE @NEXT_VENDOR_NAME VARCHAR(50)
	DECLARE @NEXT_PROCESS VARCHAR(50)
	DECLARE @VENDOR_NAME VARCHAR(50)

	SET @WIPCURSOR = CURSOR FOR SELECT LOT_NUMBER, LOT_ID, VENDOR_NAME FROM #RESULTS
		
	OPEN @WIPCURSOR
	FETCH NEXT FROM @WIPCURSOR INTO @LOT_NUMBER, @LOT_ID, @VENDOR_NAME

	WHILE @@FETCH_STATUS = 0
	BEGIN
			IF ( SELECT COUNT(*) FROM LOT_TABLE WHERE LOT_SPLIT = 0 AND LOT_NUMBER=SUBSTRING( @LOT_NUMBER, 1, LEN( SUBSTRING(@LOT_NUMBER, 2,6  ) ) ) ) > 0 
			BEGIN
					SET @LOT_NUMBER = SUBSTRING( @LOT_NUMBER, 1, LEN( SUBSTRING(@LOT_NUMBER, 2,6  ) ) )
			END
		IF @PROCESS_TYPE = 'ASSEMBLY'
		BEGIN
			
			SELECT @NEXT_VENDOR_NAME = V_NAME FROM VENDOR WHERE VENDOR_ID = (SELECT TOP 1 TEST_LOCATION_ID FROM ASSM_RELEASE_TABLE ART,ASSM_RELEASE_LOT_TABLE ARLT WHERE ART.assembly_release_ID = ARLT.assembly_release_ID AND STATUS <> 'CANCELLED' AND SHipped_LOt_NUMBER like @LOT_NUMBER+'%')
			
			SET @NEXT_PROCESS = 'FT'
		END

		IF @PROCESS_TYPE = 'FT'
		BEGIN
			--SET @NEXT_VENDOR_NAME = @VENDOR_NAME
			--SET @NEXT_PROCESS = 'FPO'
			SELECT @NEXT_VENDOR_NAME = V_NAME FROM VENDOR WHERE VENDOR_ID =(SELECT TOP 1 TEST_LOCATION_ID FROM ASSM_RELEASE_TABLE ART,ASSM_RELEASE_LOT_TABLE ARLT WHERE ART.assembly_release_ID = ARLT.assembly_release_ID AND STATUS <> 'CANCELLED' AND IS_TAPE_REEL = 0 AND SHipped_LOt_NUMBER like @LOT_NUMBER+'%') 
			SET @NEXT_PROCESS = 'Tape & Reel'
			IF @NEXT_VENDOR_NAME IS NULL
			BEGIN
					SELECT @NEXT_VENDOR_NAME = V_NAME FROM VENDOR WHERE VENDOR_ID = (SELECT TOP 1 SHIP_TO_VENDOR_ID FROM ASSM_RELEASE_TABLE ART,ASSM_RELEASE_LOT_TABLE ARLT WHERE ART.assembly_release_ID = ARLT.assembly_release_ID AND STATUS <> 'CANCELLED' AND SHipped_LOt_NUMBER like @LOT_NUMBER+'%')
					SET @NEXT_PROCESS = 'FG'
			END
			
		END	

		/*IF @PROCESS_TYPE = 'FPO'
		BEGIN
			
			SELECT @NEXT_VENDOR_NAME = V_NAME FROM VENDOR WHERE VENDOR_ID =(SELECT TOP 1 TEST_LOCATION_ID FROM ASSM_RELEASE_TABLE ART,ASSM_RELEASE_LOT_TABLE ARLT WHERE ART.assembly_release_ID = ARLT.assembly_release_ID AND STATUS <> 'CANCELLED' AND IS_TAPE_REEL = 0 AND SHipped_LOt_NUMBER like @LOT_NUMBER+'%') 
			IF @NEXT_VENDOR_NAME IS NULL
			BEGIN
					SELECT @NEXT_VENDOR_NAME = V_NAME FROM VENDOR WHERE VENDOR_ID = (SELECT TOP 1 SHIP_TO_VENDOR_ID FROM ASSM_RELEASE_TABLE ART,ASSM_RELEASE_LOT_TABLE ARLT WHERE ART.assembly_release_ID = ARLT.assembly_release_ID AND STATUS <> 'CANCELLED' AND SHipped_LOt_NUMBER like @LOT_NUMBER+'%')
			END
			SET @NEXT_PROCESS = 'Tape & Reel'
		END */

		IF @PROCESS_TYPE = 'Tape & Reel'
		BEGIN
			SELECT @NEXT_VENDOR_NAME = V_NAME FROM VENDOR WHERE VENDOR_ID = (SELECT TOP 1 SHIP_TO_VENDOR_ID FROM ASSM_RELEASE_TABLE ART,ASSM_RELEASE_LOT_TABLE ARLT WHERE ART.assembly_release_ID = ARLT.assembly_release_ID AND STATUS <> 'CANCELLED' AND SHipped_LOt_NUMBER like @LOT_NUMBER+'%')
			SET @NEXT_PROCESS = 'FG'
		END

		IF @PROCESS_TYPE = 'FG'
		BEGIN
			--SELECT @NEXT_VENDOR_NAME = V_NAME FROM VENDOR WHERE VENDOR_ID = (SELECT TOP 1 SHIP_TO_VENDOR_ID FROM ASSM_RELEASE_TABLE ART,ASSM_RELEASE_LOT_TABLE ARLT WHERE ART.assembly_release_ID = ARLT.assembly_release_ID AND STATUS <> 'CANCELLED' AND SHipped_LOt_NUMBER like @LOT_NUMBER+'%')
			SET @NEXT_PROCESS = 'Tape & Reel'
		END

		UPDATE #RESULTS SET NEXT_VENDOR = @NEXT_VENDOR_NAME , NEXT_PROCESS = @NEXT_PROCESS WHERE LOT_ID = @LOT_ID
		
		SET @NEXT_PROCESS = NULL	
		SET @NEXT_VENDOR_NAME = NULL
		SET @LOT_NUMBER = NULL
		SET @LOT_ID		= NULL
		SET @VENDOR_NAME = NULL
		FETCH NEXT FROM @WIPCURSOR INTO @LOT_NUMBER, @LOT_ID, @VENDOR_NAME 
		
	END
	CLOSE @WIPCURSOR
	DEALLOCATE @WIPCURSOR

	IF @PROCESS_TYPE = 'FG' AND @AVIOD_TR_FG_LOTS = 1
	BEGIN	
		--SELECT Count(*) FROM PRODUCT P, PROCESS_STEP PS WHERE P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND PS.PS_TYPE = 'Tape & Reel' 
		SELECT 
			 LOT_ID ,PROCESSSTEP_ID , LOCATION_ID , PART_NUMBER , Lot_Number , Fab_Lot_Number ,  WAFER_QTY_IN, QTY_IN , CONVERT(VARCHAR,DATE_IN, 101) AS DATE_IN , VENDOR_NAME, DATE_CODE, NEXT_VENDOR,NEXT_PROCESS, LOT_STATUS,LOT_COMMENTS 
			FROM #RESULTS WHERE Part_Number  NOt IN ( SELECT PART_NUMBER FROM PRODUCT P, PROCESS_STEP PS WHERE P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND PS.PS_TYPE = 'Tape & Reel' ) ORDER BY LOT_NUMBER
	END
	ELSE
	BEGIN 
			SELECT 
			 LOT_ID ,PROCESSSTEP_ID , LOCATION_ID , PART_NUMBER , Lot_Number , Fab_Lot_Number , WAFER_QTY_IN, QTY_IN , CONVERT(VARCHAR,DATE_IN, 101) AS DATE_IN , VENDOR_NAME, DATE_CODE, NEXT_VENDOR,NEXT_PROCESS, LOT_STATUS,LOT_COMMENTS 
			FROM #RESULTS ORDER BY LOT_NUMBER
	END
END